package com.lexton.util.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.ClientAnchor.AnchorType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.PictureData;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


public class ExcelReadUtils {
	private Workbook wb;
	//private Row row;
	private String ext;
	private String filePath;
	private String headerMode;
	private String layoutMode;
	private Map<Integer, String> sheetInfo;
	
	public Map<Integer, String> getSheetInfo() {
		return sheetInfo;
	}
	
	public Workbook getWb() {
		return wb;
	}

	public String getExt() {
		return ext;
	}

	public String getHeaderMode() {
		return headerMode;
	}

	public String getLayoutMode() {
		return layoutMode;
	}

	public ExcelReadUtils(String filePath, String headerMode, String layoutMode) {
		this.filePath = filePath;
		this.ext = this.filePath.substring(filePath.lastIndexOf(".")+1);
		try {
			this.headerMode = headerMode;
			this.layoutMode = layoutMode;
			
			InputStream is = new FileInputStream(filePath);
			if ("xls".equals(ext)) {  
				wb = new HSSFWorkbook(is);
			} else if ("xlsx".equals(ext)) {
				wb = new XSSFWorkbook(is);
			} else {
				wb = null;
			}
			
			sheetInfo = new HashMap<Integer, String>();
			for (int i=0; i<wb.getNumberOfSheets(); i++) {
				sheetInfo.put(i, wb.getSheetAt(i).getSheetName());
			}
		} catch (FileNotFoundException e) {
		
		} catch (IOException e) {
		
		}
	}
	
	public List<? extends PictureData> getAllPictures() throws Exception {
		if (wb == null) {
			throw new Exception("Workbook对象为空！");
		}
		return wb.getAllPictures();
	}
	
	/** 
	 * 读取Excel表格表头的内容 
	 *  
	 * @param InputStream 
	 * @return String 表头内容的数组 
	 * @author zengwendong 
	 */
	public String[] readExcelTitle() throws Exception {
		if (wb == null) {
			throw new Exception("Workbook对象为空！");
		}
		Sheet sheet = wb.getSheetAt(0);
		Row row = sheet.getRow(0);
		// 标题总列数  
		int colNum = row.getPhysicalNumberOfCells();
		System.out.println("colNum:" + colNum);
		String[] title = new String[colNum];
		for (int i = 0; i < colNum; i++) {
			// title[i] = getStringCellValue(row.getCell((short) i));
			title[i] = row.getCell(i).getCellFormula();
		}
		return title;
	}
	
	/**
	 * @author Jimmy qq396040860 mobile18674842761
	 * @throws Exception 
	 */
	public List<Map<Integer, Map<Integer, Object>>> readExcelContent(Map<Integer, String> pickedSheets) throws Exception {
		List<Map<Integer, Map<Integer, Object>>> result = new ArrayList<Map<Integer, Map<Integer, Object>>>();
		
		pickedSheets.forEach((i, o)->{
			try {
				if ("STYLE01".equals(layoutMode)) {
					result.add(this.readExcelContent(i));
				} else if ("STYLE02".equals(layoutMode)) {
					result.add(this.readExcelContent02(i));
				} else if ("STYLE03".equals(layoutMode)) {
					result.add(this.readExcelContent03(i));
				}
			} catch (Exception e) {
				e.printStackTrace();
				throw new RuntimeException(e.getMessage());
			}
		});
		
		return result;
	}
	
	private Map<Integer, Map<Integer, Object>> readExcelContent03(Integer sheetIndex) throws UnsupportedEncodingException {
		Map<Integer, Map<Integer,Object>> content = new HashMap<Integer, Map<Integer,Object>>();
		//表头
		content.put(0, readExcelHeader(sheetIndex));
		
		Sheet sheet = wb.getSheetAt(sheetIndex);
		
		//Key行列信息
		Map<String, List<PictureData>> pictures = ExcelPictureUtil.getSheetPictrues(wb, sheet, sheetIndex, ext);
		
		Iterator<String> iter = pictures.keySet().iterator();
		while (iter.hasNext()) {
			String key = iter.next();
			
			String[] cell = key.split("_");
			Integer firstRow = Integer.parseInt(cell[1]);
			Integer firstCol = Integer.parseInt(cell[2]);
			
			int j = 0;
			//lastR+1 编码名称
			Row snNameRow = sheet.getRow(firstRow+1);
			Object snName = getCellFormatValue(snNameRow.getCell(firstCol));
			String sn = snName.toString().split("\\s+")[0];
			String name = "";
			if (snName.toString().split("\\s+").length == 2) {
				name = snName.toString().split("\\s+")[1];
			}
			
			//lastR+2 规格与价格
			Row specPriceRow = sheet.getRow(firstRow+2);
			Object value = getCellFormatValue(specPriceRow.getCell(firstCol));
			
			String lastPrice = "";
			if (value != null) {
				//是否存在换行，要是有多行则把每行做为一个规格
				String[] strs = value.toString().split("\n");
				for (String str : strs) {
					if (str == null || str.trim().equals("")) {
						continue;
					}
					String[] specPrices = str.split("\\s+");
					
					if (specPrices.length==2) {
						lastPrice = specPrices[1];
					}
					
					Map<Integer, Object> cellValue = new HashMap<Integer, Object>();
					cellValue.put(j++, sn);
					cellValue.put(j++, name);
					cellValue.put(j++, specPrices[0]);
					cellValue.put(j++, lastPrice);
					
					List<PictureData> list = pictures.get(key);
					if (list != null && list.size() > 0) {
						StringBuilder sb = new StringBuilder();
						for (PictureData data : list) {
							sb.append("jiangtao" + new String(data.getData(), "ISO-8859-1"));
						}
						cellValue.put(j++, sb.toString().substring("jiangtao".length()));
					}
					content.put(content.size(), cellValue);
				}
				j=0;
			}
		}
		return content;
	}

	private Map<Integer, Object> readExcelHeader(int sheetIndex) {
		Map<Integer, Object> result = new HashMap<Integer, Object>();
		Row row = null;
		if ("FIRST_SHEET".equals(headerMode)) {
			row = wb.getSheetAt(0).getRow(0);
		} else if ("EVERY_SHEET".equals(headerMode)) {
			row = wb.getSheetAt(sheetIndex).getRow(0);
		}
		
		for (int i=0; i<=row.getPhysicalNumberOfCells(); i++) {
			result.put(i, getCellFormatValue(row.getCell(i)));
		}
		return result;
	}

	/**
	 * 读取Excel数据内容 
	 * @param InputStream
	 * @return Map 包含单元格数据内容的Map对象 
	 * @author zengwendong 
	 * @param picture 
	 */  
	public Map<Integer, Map<Integer, Object>> readExcelContent02(Integer sheetIndex) throws Exception {
		if (wb == null) {
			throw new Exception("Workbook对象为空！");
		}
		
		Map<Integer, Map<Integer,Object>> content = new HashMap<Integer, Map<Integer,Object>>();
		//表头
		content.put(0, readExcelHeader(sheetIndex));
		
		Sheet sheet = wb.getSheetAt(sheetIndex);
		
		//Key行列信息
		Map<String, List<PictureData>> pictures = ExcelPictureUtil.getSheetPictrues(wb, sheet, sheetIndex, ext);
		
		Iterator<String> iter = pictures.keySet().iterator();
		while (iter.hasNext()) {
			String key = iter.next();
			
			String[] cell = key.split("_");
			Integer firstRow = Integer.parseInt(cell[1]);
			Integer firstCol = Integer.parseInt(cell[2]);
			
			Map<String, Object> info = ExcelReadUtils.getMergedInfo(sheet, sheet.getRow(firstRow).getCell(firstCol));
			
			//result.put("isMergedRegion", true);
			//int firstC = Integer.parseInt(info.get("firstColumn").toString());
			int lastC = Integer.parseInt(info.get("lastColumn").toString());
			int firstR = Integer.parseInt(info.get("firstRow").toString());
			int lastR = Integer.parseInt(info.get("lastRow").toString());
			
			Map<Integer, Object> cellValue = new HashMap<Integer, Object>();
			
			int j = 0;
			for (int i = firstR; i <= lastR; i++) {
				Row row = sheet.getRow(i);
				Object value = getCellFormatValue(row.getCell(lastC+2));
				cellValue.put(j++, value);
			}
			List<PictureData> list = pictures.get(key);
			if (list != null && list.size() > 0) {
				StringBuilder sb = new StringBuilder();
				for (PictureData data : list) {
					sb.append("jiangtao" + new String(data.getData(), "ISO-8859-1"));
				}
				cellValue.put(j++, sb.toString().substring("jiangtao".length()));
			}
			content.put(content.size(), cellValue);
		}
		
		return content;
	}
	
	/**
	 * 读取Excel数据内容 
	 * @param InputStream
	 * @return Map 包含单元格数据内容的Map对象 
	 * @author zengwendong 
	 * @param picture 
	 */  
	public Map<Integer, Map<Integer, Object>> readExcelContent(Integer sheetIndex) throws Exception {
		if (wb == null) {
			throw new Exception("Workbook对象为空！");
		}
		
		Map<Integer, Map<Integer,Object>> content = new HashMap<Integer, Map<Integer,Object>>();
		//表头
		Map<Integer, Object> headerRow = readExcelHeader(sheetIndex);
		if ("FIRST_SHEET".equals(headerMode) && sheetIndex > 0)
			content.put(0, headerRow);
		
		Sheet sheet = wb.getSheetAt(sheetIndex);
		
		//Key行列信息
		Map<String, List<PictureData>> pictures = ExcelPictureUtil.getSheetPictrues(wb, sheet, sheetIndex, ext);
		
		// 得到总行数  
		int rowNum = sheet.getLastRowNum();
		int colNum = headerRow.size();
		for (int i=0; i <= rowNum; i++) {
			Row row = sheet.getRow(i);
			if (row == null) {
				//最后一行和前面的行之间有空白行，那么row存在为空的可能性
				continue;
			}
			int j = 0;
			Map<Integer,Object> cellValue = new HashMap<Integer, Object>();
			while (j < colNum) {
				Cell cell = row.getCell(j);
				Map<String, Object> merge = getMergedInfo(sheet, cell);
				if ((boolean)merge.get("isMergedRegion")) {
					Integer firstRow = Integer.valueOf(merge.get("firstRow").toString());
					Integer firstColumn = Integer.valueOf(merge.get("firstColumn").toString());
					if (firstRow == i && firstColumn == j) {
						cellValue.put(j, getCellFormatValue(cell));
					} else if (content.get(firstRow) == null) {
						cellValue.put(j, null);
					} else {
						cellValue.put(j,  content.get(firstRow).get(firstColumn));
					}
				} else {
					cellValue.put(j, getCellFormatValue(cell));
				}
				
				//看看这个空格中是不是放的图片，图片序列化并以jiangtao分割
				if (cellValue.get(j) == null || "".equals(cellValue.get(j).toString())) {
					List<PictureData> list = pictures.get(sheetIndex + "_" + (i) + "_" + (j));
					
					if (list != null && list.size() > 0) {
						StringBuilder sb = new StringBuilder();
						for (PictureData data : list) {
							sb.append("jiangtao" + new String(data.getData(), "ISO-8859-1"));
						}
						cellValue.put(j, sb.toString().substring("jiangtao".length()));
					}
				}
				j++;
			}
			
			content.put(content.size(), cellValue);
		}
		return content;
	}
	
	/**
	 * 判断是否在合并单元格内，并返回合并的单元格的信息
	 */
	public static Map<String, Object> getMergedInfo(Sheet sheet, Cell cell) {
		Map<String, Object> result = new HashMap<String, Object>();
		result.put("isMergedRegion", false);
		if (cell == null) return result;
		// 得到一个sheet中有多少个合并单元格
		int sheetmergerCount = sheet.getNumMergedRegions();
		for (int i = 0; i < sheetmergerCount; i++) {
			// 得出具体的合并单元格
			CellRangeAddress ca = sheet.getMergedRegion(i);
			// 得到合并单元格的起始行, 结束行, 起始列, 结束列
			int firstC = ca.getFirstColumn();
			int lastC = ca.getLastColumn();
			int firstR = ca.getFirstRow();
			int lastR = ca.getLastRow();
			// 判断该单元格是否在合并单元格范围之内, 如果是, 则返回 true
			if (cell.getColumnIndex() <= lastC && cell.getColumnIndex() >= firstC) {
				if (cell.getRowIndex() <= lastR && cell.getRowIndex() >= firstR) {
					result.put("isMergedRegion", true);
					result.put("firstColumn", firstC);
					result.put("lastColumn", lastC);
					result.put("firstRow", firstR);
					result.put("lastRow", lastR);
					return result;
				}
			}
		}
		return result;
	}
	
	/** 
	 * 根据Cell类型设置数据 
	 * @param cell
	 * @return
	 * @author zengwendong
	 */
	public static Object getCellFormatValue(Cell cell) {
		Object cellvalue = "";
		if (cell != null) {
			CellType cellType = cell.getCellTypeEnum();
			if (cellType == CellType.NUMERIC || cellType == CellType.FORMULA) {
				if (DateUtil.isCellDateFormatted(cell)) {
					// 如果是Date类型则，转化为Data格式  
					// data格式是带时分秒的：2013-7-10 0:00:00
					// cellvalue = cell.getDateCellValue().toLocaleString();
					// data格式是不带带时分秒的：2013-7-10  
					Date date = cell.getDateCellValue();
					cellvalue = date;  
				} else {// 如果是纯数字 
					// 取得当前Cell的数值  
					cellvalue = String.valueOf(Math.round(cell.getNumericCellValue()));
				}
			} else if (cellType == CellType.STRING) {
				cellvalue = cell.getRichStringCellValue().getString();
			} else {
				cellvalue = cell.getStringCellValue();
			}
		} else {
			cellvalue = "";
		}
		return cellvalue;
	}
	
	/**
	 * sheetInfo: 用户界面选择的工作表索引及名称
	 * list：选择的工作表的内容
	 * @param exportPath 
	 */
	public static Workbook writeToExcel(Map<Integer, String> sheetInfo, List<Map<Integer, Map<Integer,Object>>> list) throws IOException {
		Workbook wb = new XSSFWorkbook();
		//list中Map的顺序和pickedSheets顺序是对应的
		for (int i=0; i<list.size(); i++) {
			Map<Integer, Map<Integer,Object>> data = list.get(i);
			
			Sheet sheet = null;
			
			Iterator<Integer> iter = sheetInfo.keySet().iterator();
			int keyIndex = 0;
			while(iter.hasNext()) {
				String sheetName = sheetInfo.get(iter.next());
				if (i == keyIndex) {
					sheet = wb.createSheet(sheetName);
					sheet.autoSizeColumn(15, true);
					break;
				}
				keyIndex++;
			}
			
			CellStyle headerCellStyle = wb.createCellStyle();
			headerCellStyle.setWrapText(true);
			headerCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
			headerCellStyle.setAlignment(HorizontalAlignment.CENTER);
			Font headerFont = wb.createFont();
			headerFont.setFontName("黑体");
			headerFont.setFontHeightInPoints((short) 16);//设置字体大小 
			headerCellStyle.setFont(headerFont);
			
			CellStyle cellStyle = wb.createCellStyle();
			cellStyle.setWrapText(true);
			cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
			cellStyle.setAlignment(HorizontalAlignment.CENTER);
			Font font = wb.createFont();
			font.setFontName("宋体");
			font.setFontHeightInPoints((short)14);//设置字体大小 
			cellStyle.setFont(font);
			
			Drawing<?> patriarch = sheet.createDrawingPatriarch();
			
			Iterator<Integer> rowIter = data.keySet().iterator();
			while (rowIter.hasNext()) {
				Integer j = rowIter.next();
				Map<Integer, Object> o = data.get(j);
				Row row = sheet.createRow(j);
				row.setHeightInPoints(80);
				for (int k=0; k<o.size()-1; k++) {
					Cell cell = row.createCell(k);
					if (j==0) cell.setCellStyle(headerCellStyle);
					else {
						cell.setCellStyle(cellStyle);
					}
					cell.setCellValue(o.get(k) == null ? null : o.get(k).toString());
				}
				
				try {
					String[] pictureStrings = o.get(o.size()-1).toString().split("jiangtao");
					for (int k=0; k<pictureStrings.length; k++) {
						String s = pictureStrings[k];
						ClientAnchor anchor = new XSSFClientAnchor(5, 5, 5, 5, (short)o.size()+k-1, j, (short)o.size()+k,j+1);
						//在Excel中是否可以跟随着移动和改变尺寸
						anchor.setAnchorType(AnchorType.MOVE_AND_RESIZE);
						patriarch.createPicture(anchor, wb.addPicture((s.getBytes("ISO-8859-1")), Workbook.PICTURE_TYPE_JPEG));
					}
					
				} catch (UnsupportedEncodingException e) {
					e.printStackTrace();
				}
			}
		}
		
		return wb;
	}
	
	@SuppressWarnings("deprecation")
	public static void writeToExcel(Map<Integer, Map<Integer,Object>> data) throws IOException {
		Workbook wb = new XSSFWorkbook();
		Sheet sheet = wb.createSheet("Sheet1");
		
		CellStyle headerCellStyle = wb.createCellStyle();
		headerCellStyle.setWrapText(true);
		headerCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		headerCellStyle.setAlignment(HorizontalAlignment.CENTER);
		Font headerFont = wb.createFont();
		headerFont.setFontName("黑体");
		headerFont.setFontHeightInPoints((short) 16);//设置字体大小 
		headerCellStyle.setFont(headerFont);
		
		CellStyle cellStyle = wb.createCellStyle();
		cellStyle.setWrapText(true);
		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
		Font font = wb.createFont();
		font.setFontName("宋体");
		font.setFontHeightInPoints((short) 14);//设置字体大小 
		cellStyle.setFont(font);
		
		Drawing<?> patriarch = sheet.createDrawingPatriarch();
		data.forEach((i, o)->{
			Row row = sheet.createRow(i);
			//row.setHeight((short)80);
			row.setHeightInPoints(60);
			for (int j=0; j<o.size()-1; j++) {
				Cell cell = row.createCell(j);
				if (i==0) cell.setCellStyle(headerCellStyle);
				else {
					cell.setCellStyle(cellStyle);
				}
				cell.setCellValue(o.get(j)==null?null:o.get(j).toString());
			}

			try {
				String[] pictureStrings = o.get(o.size()-1).toString().split("jiangtao");
				for (int k=0; k<pictureStrings.length; k++) {
					String s = pictureStrings[k];
					ClientAnchor anchor = new XSSFClientAnchor(0,0,0,0,(short)o.size()+k-1,i,(short)o.size()+k,i+1);
					anchor.setAnchorType(2);
					patriarch.createPicture(anchor,wb.addPicture((s.getBytes("ISO-8859-1")),Workbook.PICTURE_TYPE_JPEG));
				}
				
			} catch (UnsupportedEncodingException e) {
				e.printStackTrace();
			}
		});
		
		File file = new File("C:/Users/Jimmy/Desktop/分析结果.xlsx");
		FileOutputStream outStream = new FileOutputStream(file);
		wb.write(outStream);
		outStream.flush();
		outStream.close();
		wb.close();
		System.out.println("Excel 2007文件导出完成！导出文件路径："+file.getPath());
	}

	/**
	 * 列出工作表名称，方便选择
	 * @param filePath
	 * @return
	 */
	public static Map<Integer, String> readSheetInfo(String filePath) {
		Map<Integer, String> result = new HashMap<Integer, String>();
		
		try {
			InputStream in = new FileInputStream(filePath);
			Workbook wb = null;
			
			if (filePath.toLowerCase().endsWith("xls")) {
				wb = new HSSFWorkbook(in);
			} else {
				wb = new XSSFWorkbook(in);
			}
			
			for (int i=0; i<wb.getNumberOfSheets(); i++) {
				result.put(i, wb.getSheetAt(i).getSheetName());
			}
			
			wb.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return result;
	}
	
	public void exportImages(Map<Integer, String> pickedSheets, String exportPath) throws Exception {
		Iterator<Integer> iter = pickedSheets.keySet().iterator();
		int i=0;
		while(iter.hasNext()) {
			String s = pickedSheets.get(iter.next());
			Map<String, List<PictureData>> pictures = ExcelPictureUtil.getSheetPictrues(wb, wb.getSheetAt(i), i, ext);
			ExcelPictureUtil.printPicture(exportPath+s, pictures);
			i++;
		}
	}
	
	public void exportExcel(Workbook wb, String exportPath) throws IOException {
		File file = new File(exportPath+"分析结果.xlsx");
		FileOutputStream outStream = new FileOutputStream(file);
		wb.write(outStream);
		outStream.flush();
		outStream.close();
		wb.close();
		System.out.println("Excel 2007文件导出完成！导出文件路径："+file.getPath());
	}
	
	public static void main(String[] args) {
//		try {
//			String filepath = "C:/Users/Jimmy/Desktop/供应商/2016海景家具报价表20160819.xls";
//			//ExcelReadUtils excelReader = new ExcelReadUtils(filepath);
//			// 对读取Excel表格标题测试  
////			String[] title = excelReader.readExcelTitle();
////			System.out.println("获得Excel表格的标题:");
////			for (String s : title) {
////				System.out.print(s + " ");
////			}
//
//			// 对读取Excel表格内容测试  
//			//Map<Integer, Map<Integer,Object>> map = excelReader.readExcelContent(0);
//			System.out.println("获得Excel表格的内容:");
//			//for (int i = 1; i <= map.size(); i++) {
//			//	System.out.println(map.get(i));
//			//}
//		} catch (FileNotFoundException e) {
//			System.out.println("未找到指定路径的文件!");
//			e.printStackTrace();
//		} catch (Exception e) {
//			e.printStackTrace();
//		}
	}

}
